/**
 * @Author: lena
 * @Date: 2021/10/17 20:58
 * @Description: sqlx库的使用
 * @Version: 1.0.0
 */

package sqlx

import (
	"fmt"

	_ "github.com/go-sql-driver/mysql" // 必须导入否则无法识别mysql
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

/**
创建student结构体用于操作数据库表student
create table student (
	id int primary key,
	name varchar(20),
	age int
)
*/
type Student struct {
	Id   int
	Name string
	Age  int
}

// 连接数据库
func init() {
	// 需要定义err 为了避免在下面connect时使用":=" 出现全局变量db的错误
	var err error
	// 连接
	url := "root:12345678@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	db, err = sqlx.Connect("mysql", url)
	if err != nil {
		fmt.Println("connect err :", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	fmt.Println("conn db success!")
}

// QueryOne 查询单行：func (db *DB) Get(dest interface{}, query string, args ...interface{}) error
func QueryOne() {
	sql := "select * from student where id = ?"
	var s Student
	// 如果把init方法放到同包另一个文件 执行了方法 但在这里db就为nil...
	if db == nil {
		fmt.Println("db is nil")
		return
	}
	err := db.Get(&s, sql, 1001)
	if err != nil {
		fmt.Println("db get err :", err) // 属性没有大写：scannable dest type struct with >1 columns (3) in result
		return
	}
	fmt.Println(s)
}

// Query 查询多行：func (db *DB) Select(dest interface{}, query string, args ...interface{}) error
func Query() {
	sql := "select * from student"
	var s []Student
	err := db.Select(&s, sql)
	if err != nil {
		fmt.Println("db get err :", err)
		return
	}
	for index, stu := range s {
		fmt.Println(index, stu)
	}
}

// NamedExec 更新操作的sql语句与map或结构体同名变量的绑定：func (db *DB) NamedExec(query string, arg interface{}) (sql.Result, error)
func NamedExec() {
	sqlStr := "insert into student(name,age) VALUES (:name,:age)"
	rs, err := db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "tity",
			"age":  22,
		})
	if err != nil {
		fmt.Println("insert err :", err)
		return
	}
	fmt.Println(rs.LastInsertId())
}

// NamedQuery 查询的sql语句绑定
func NamedQuery() {
	sql := "select * from student where id = :id"
	s := Student{Id: 1001}
	rows, err := db.NamedQuery(sql, s)
	if err != nil {
		fmt.Println("query err :", err)
		return
	}
	// 将查询结果存入s
	rows.Next() // 第一行是属性名
	rows.StructScan(&s)
	fmt.Println(s)
}

func BatchInsert() {
	// 开启事务
	tx, err := db.Begin()
	if err != nil {
		fmt.Println("db begin err:", err)
		return
	}
	sqlStr := "insert into student(name,age) VALUES (?,?)"
	// 预加载sql
	stmt, err := tx.Prepare(sqlStr)
	if err != nil {
		fmt.Println("tx prepare err:", err)
		return
	}
	// 批处理sql
	for i := 0; i < 10; i++ {
		_, err = stmt.Exec("lena", i)
		if err != nil {
			fmt.Println("stmt exec err:", err)
			return
		}
	}
	// 提交事务
	if err := tx.Commit(); err != nil {
		fmt.Println("commit err:", err)
		return
	}
}

/** 代码报错
  批量插入：sqlx.In
  前提：需要结构体实现driver.Valuer接口
      func (u User) Value() (driver.Value, error) {
          return []interface{}{u.Name, u.Age}, nil
      }
*/
// 实现driver.Valuer接口
/*func (s Student) Value() (driver.Value, error) {
    return []interface{}{s.Name, s.Age}, nil
}*/

// 批量插入：sqlx.In
/*func In() {
    var stu []interface{}
    stu=append(stu, Student{Name: "hello",Age: 12})
    stu=append(stu, Student{Name: "in",Age: 12})
    fmt.Println(stu)
    sql:="insert into student(name,age) value(?,?),(?,?)"
    query, arg, err := sqlx.In(sql, stu)
    if err != nil {
        fmt.Println("sqlx in err :",err)    // number of bindVars exceeds arguments
        return
    }
    fmt.Println("sql:",query)
    fmt.Println("arg:",arg)
}*/
